package com.telsoft.hms.data;

import java.util.ArrayList;
import java.util.List;

import com.telsoft.hms.entity.AMObject;
import com.telsoft.hms.entity.ETT_Language;
import com.telsoft.hms.entity.ETT_Widget;
import com.telsoft.hms.lib.admin.client.data.ClientDataPreprocessor;

/**
 * 
 * @author VIETPH, KIENNT, MANHNT 7/10/2014
 *
 */
public class Widget_DATA extends ClientDataPreprocessor {

	/**
	 * @author VIETPH 7/10/2014
	 * Phương thức lấy ra danh sách Widget mà User có quyền
	 * @param lUserId ID người đăng nhập được lấy ra trong Session trên WS
	 * @param strHospitalKey mã bệnh viện
	 * @return Danh sách AMObject chức năng
	 * @throws Exception mã lỗi ORA-xxx (Oracle Exception), POOL-xxxx (Exception quản lý pool)
	 */
	public AMObject[] getWidgetRightByUser(long lUserId,
			String strHospitalKey) throws Exception {
		List<AMObject> lstWidget = new ArrayList<>();
		String strSql = "SELECT sm.OBJECT_ID,sm.NAME,sm.PARENT_ID,sm.PATH, MOD (MIN (2 * child_level + access_type), 2) access_type"
				+ " FROM AM_OBJECT sm,"
				+ " (SELECT SUM.OBJECT_ID, SUM.right_code, SUM.access_type, 0 child_level"
				+ " FROM AM_USER_OBJECT SUM, AM_OBJECT_right smr"
				+ " WHERE SUM.OBJECT_ID = smr.OBJECT_ID"
				+ " AND SUM.right_code = smr.right_code"
				+ " AND SUM.user_id =  ?"
				+ " UNION ALL"
				+ " SELECT smr.OBJECT_ID, smr.right_code, smr.access_type, 1000 child_level"
				+ " FROM AM_OBJECT_right smr WHERE smr.access_type > 0"
				+ " UNION ALL"
				+ " SELECT DISTINCT sgm.OBJECT_ID, sgm.right_code, sgm.access_type, sg.child_level"
				+ " FROM AM_GROUP_OBJECT sgm, AM_OBJECT_right smr,"
				+ " (SELECT sg.GROUP_ID, LEVEL child_level"
				+ " FROM AM_GROUP sg "
				+ " WHERE NVL (status, 0) > 0"
				+ " START WITH sg.GROUP_ID IN ("
				+ " SELECT sgu.GROUP_ID FROM AM_GROUP_USER sgu"
				+ " WHERE sgu.user_id = ?)"
				+ " CONNECT BY PRIOR sg.PARENT_ID = sg.GROUP_ID) sg"
				+ " WHERE sgm.GROUP_ID = sg.GROUP_ID"
				+ " AND sgm.OBJECT_ID = smr.OBJECT_ID) smr"
				+ " WHERE sm.OBJECT_ID = smr.OBJECT_ID"
				+ " AND nvl(sm.status,0) > 0 AND PARENT_ID = (SELECT distinct a.object_id FROM am_object a WHERE a.PATH ='widget')"
				+ " GROUP BY sm.NAME,sm.OBJECT_ID,sm.PARENT_ID,sm.PATH"
				+ " HAVING MOD (MIN (2 * child_level + access_type), 2) > 0";
		try {
			open(strHospitalKey);
			mStmt = mConnection.prepareStatement(strSql);
			mStmt.setLong(1, lUserId);
			mStmt.setLong(2, lUserId);
			mRs = mStmt.executeQuery();
			while (mRs.next()) {
				AMObject widget = new AMObject();
				widget.setObjectID(mRs.getLong("OBJECT_ID"));
				widget.setName(mRs.getString("NAME"));
				widget.setPath(mRs.getString("PATH"));

				lstWidget.add(widget);
			}
		} catch (Exception ex) {
			throw new Exception(ex.getMessage());
		} finally {
			close(mConnection, mStmt, mRs);
		}
//		lLanguage.toArray(new ETT_Language[lLanguage.size()])
		return lstWidget.toArray(new AMObject[lstWidget.size()] );
	}

	/**
	 * ManhNT 7/10/2014 
	 * Phương thức lấy tất cả các Widget theo người dùng đã chọn
	 * @param strKey Mã bệnh viện
	 * @param userId Id người đăng nhập lấy ra trong Session WS
	 * @throws Exception Mã lỗi ORA-xxxx (Oracle Exception)
	 */
	public List<ETT_Widget> getAllWidget(String strHospitalKey, long lUserId)
			throws Exception {
		List<ETT_Widget> lstWidget = new ArrayList<>();
		String strQuery = "SELECT cf.\"VALUES\" from AM_CONFIG cf where cf.\"TYPE\" = 'DASHBOARD' and cf.USER_ID = ?";
		try {
			open(strHospitalKey);
			mStmt = mConnection.prepareStatement(strQuery);
			mStmt.setLong(1, lUserId);
			mRs = mStmt.executeQuery();
			while (mRs.next()) {
				ETT_Widget objWidget = new ETT_Widget();
				objWidget.setValue(mRs.getString("VALUES"));
				lstWidget.add(objWidget);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			close(mConnection, mStmt, mRs);
		}
		return lstWidget;
	}
	
	/**
	 * KienNT 7/10/2014 
	 * Phương thức cập nhật vị trí widget của người dùng
	 * @param strHospitalKey Mã bệnh viện
	 * @param lUserId Id người đăng nhập lấy ra trong Session WS
	 * @throws Exception Mã lỗi ORA-xxxx (Oracle Exception)
	 */
	public boolean updateWidgetLocation(String strHospitalKey, long lUserId,
			List<ETT_Widget> lstWidget) throws Exception {
		String strDelete = "DELETE FROM AM_CONFIG WHERE USER_ID = ? AND TYPE = 'DASHBOARD'";
		String strInsert = "INSERT INTO AM_CONFIG (USER_ID, TYPE, \"VALUES\") VALUES (?, 'DASHBOARD', ?)";

		try {
			open(strHospitalKey);
			mConnection.setAutoCommit(false);
			// xoá hết dòng dữ liệu liên quan đến user
			mStmt = mConnection.prepareStatement(strDelete);
			mStmt.setLong(1, lUserId);
			mStmt.executeUpdate();
			mStmt.clearBatch();

			// Check list dữ liệu truyền vào không null size > 0 thì insert
			if (lstWidget != null && lstWidget.size() > 0) {
				mStmt = mConnection.prepareStatement(strInsert);
				for (int i = 0; i < lstWidget.size(); i++) {
					String value = lstWidget.get(i).getId() + ":"
							+ lstWidget.get(i).getPosition();
					mStmt.setLong(1, lUserId);
					mStmt.setString(2, value);
					mStmt.addBatch();
				}
				mStmt.executeBatch();
				mStmt.clearBatch();
			}

			mConnection.commit();
		} catch (Exception ex) {
			mConnection.rollback();
			throw new Exception(ex.getMessage());
		} finally {
			mConnection.setAutoCommit(false);
			close(mConnection, mStmt, mRs);
		}
		return true;
	}
}
